package com.trkj.dao.jpa;

import java.math.BigDecimal;
import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import com.trkj.pojos.Tbstaff;

public interface IHwcStaffDAO extends CrudRepository<Tbstaff, Long> {
	
	/**
	 * 根据传入的账号查找该员工科室的其他员工
	 * @return
	 */
	//@Query("select s from Tbstaff s where s.departmentNo = (select s.departmentNo from Tbstaff where s.staffAccount = ?1)")
	//@Query("select s from Tbstaff s order by s.staffNo desc")	
	@Query("select new	Tbstaff(s.staffNo,s.departmentNo,s.staffAccount,s.staffAge,s.staffIdcard,s.staffName,s.staffPassword,s.staffPhone,s.staffPostNo,s.staffRole,s.staffSex,s.staffState,s.staffTitle) from  Tbstaff s")
	public List<Tbstaff> findAccountByStaff();
	
	/**
	 * 根据员工ID查询员工
	 * @param staffAccount
	 * @return
	 */
	@Query("select s from Tbstaff s where s.staffNo = ?1")
	public Tbstaff findIdByStaff(long staffNo);
	/**
	 * 员工的分页查询
	 */
	@Query("select s from Tbstaff s where s.staffNo!=9999 order by s.staffNo	desc")
	public List<Tbstaff> findTbstaffPage(Pageable Pageble);
	
	/**
	 * 员工的分页查询不包含离职
	 */
	@Query("select s from Tbstaff s where s.staffState != '离职' order by s.staffNo	desc")
	public List<Tbstaff> findTbstaffPageNoOut(Pageable Pageble);
	/**
	 * 员工的姓名与分页之模糊查询查询
	 * @param pagerble
	 * @param staffName
	 * @return
	 */
	@Query("select s from Tbstaff s  where s.staffName like %?1% order by s.staffNo desc")
	public List<Tbstaff> findStaffNameByPage(Pageable pagerble,String staffName);
	
	/**
	 * 根据名称模糊查询员工 不包含离职
	 * @param pagerble
	 * @param staffName
	 * @return
	 */
	@Query("select s from Tbstaff s  where s.staffName like %?1% and s.staffState != '离职' order by s.staffNo desc")
	public List<Tbstaff> findStaffNameByPageNoOut(Pageable pagerble,String staffName);
	/**
	 * 根据科室名查询属于该科室的员工不包含离职
	 * @param pagerble
	 * @param staffName
	 * @return
	 */
	@Query("select s from Tbstaff s where s.departmentNo in (select d.departmentNo from Tbdepartment d where d.departmentName like %?1%) and s.staffState != '离职' order by s.staffNo desc")
	public List<Tbstaff> findStaffDeptByPageNoOut(Pageable pagerble,String departmentName);
	
	/**
	 * 根据科室名称模糊查询员工
	 * @param pagerble
	 * @param staffName
	 * @return
	*/
	 
	@Query("select s from Tbstaff s where s.departmentNo in (select d.departmentNo from Tbdepartment d where d.departmentName like %?1%)")
	public List<Tbstaff> findDeptNameByPage(Pageable pagerble,String departmentName);
	
	
	/**
	 * 根据员工离职状态查询
	 */
	@Query("select s from Tbstaff s where s.staffState = '离职'")
	public List<Tbstaff> findByStaffState(String staffState);
	/**
	 * 通过部门编号查询员工集合
	 * @param departmentNo
	 * @return
	 */
	@Query("select s from Tbstaff s where s.departmentNo = ?1 and s.staffState != '离职'")
	public List<Tbstaff> findDeptNoByStaff(BigDecimal departmentNo);
	
}
